********************************************************************************
** 	TITLE:		CA08_results                                                  ** 
**	AUTHOR:	    Philippe Mongrain                                             **
**	DATA:       CA08_results                                                  **
**	VERSION:	Stata 16					                                  **
**	DATE:		October 2022  				                                  **
********************************************************************************

* Version control

version 16.0

* Open log file

capture log close
log using "CA08_results.smcl", replace

* 1. Download the "table_tableau12.csv" file from this link: https://www.elections.ca/scripts/OVR2008/31/data/table_tableau12.csv and rename "table_tableau12.csv" to "CA08_results.csv"
* 2. Save the "CA08_results.csv" file to the Excel format (i.e., "CA08_results.xlsx"")
* 3. Verify that the name of the Excel sheet is "Sheet1"

import excel "CA08_results.xlsx", sheet("Sheet1") firstrow clear

* Rename and (re)create variables

rename districtnumber districtnumber08
gen residence08 = residencetown + "," + residenceprovince

destring numberofvotespercent08, replace
destring majoritypercent08, replace

* Generate dichotomous variable for each party

gen lib08 = strpos(candidate08, "Liberal/Libéral") > 0
gen con08 = strpos(candidate08, "Conservative/Conservateur") > 0
gen ndp08 = strpos(candidate08, "NDP-New Democratic Party/NPD-Nouveau Parti démocratique") > 0
gen green08 = strpos(candidate08, "Green Party/Parti vert") > 0
gen bq08 = strpos(candidate08, "Bloc Québécois/Bloc Québécois") > 0

* Generate party variable

gen party08 = 1 if lib08 == 1
replace party08 = 2 if con08 == 1
replace party08 = 3 if ndp08 == 1
replace party08 = 4 if bq08 == 1
replace party08 = 5 if green08 == 1
replace party08 = 88 if party08 == . 

* Generate rank of parties

gsort districtname08 -numberofvotes08
bysort districtname08 : gen rank08 = _n

gen first08 = party08 if rank08 == 1
gen second08 = party08 if rank08 == 2
gen third08 = party08 if rank08 == 3

bysort districtname08 : gen winner08 = first[1]
bysort districtname08 : gen runnerup08 = second[2]
bysort districtname08 : gen thirdplace08 = third[3]

* Generate margins of victory

bysort districtname08 : gen margin08 = numberofvotespercent08[1] - numberofvotespercent08[2]

* Generate effective number of electoral parties

gen sqrvotepr08 = (numberofvotespercent08/100)^2

bysort districtname08 : egen sumsqrvotepr08 = sum(sqrvotepr08)

gen enep08 = 1/sumsqrvotepr08

* Labelling parties

label define party 1 "Liberal Party" 2 "Conservative Party" 3 "New Democratic Party" 4 "Bloc Quebecois" 5 "Green Party" 88 "Other"
label values winner08 runnerup08 thirdplace08 party

* Selecting, ordering, and sorting variables

keep districtnumber08 districtname08 party08 rank08 numberofvotes08 numberofvotespercent08 margin08 enep08 winner08 runnerup08 thirdplace08

order districtnumber08 districtname08 party08 rank08 numberofvotes08 numberofvotespercent08 margin08 enep08 winner08 runnerup08 thirdplace08

sort districtnumber08 rank08

drop if rank08 > 3

* Drop duplicates

duplicates tag districtnumber08, gen(dup)
duplicates drop districtnumber08, force
drop dup

* Create election variable

gen election08 = "2008 Canadian federal election"

* Saving file

save CA08_results, replace

* Merge with CA06_results.dta

merge 1:1 _n using CA0406_results.dta

rename _merge _merge06_08

* Generate variable for incumbent party reelection

gen reelected08 = 1 if winner08 == winner06
replace reelected08 = 0 if reelected08!=1

save CA040608_results, replace

log close